package com.lanyou.cook.repository;

import java.util.Collection;
import java.util.List;

import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.rest.core.annotation.RestResource;
import org.springframework.data.repository.query.Param;

import com.lanyou.cook.entity.domain.SystemInterface;

public interface SystemInterfaceRepository extends JpaRepository<SystemInterface, String>, JpaSpecificationExecutor<SystemInterface> {
	
	@Query(nativeQuery = true, value ="SELECT f2.field_id,f2.field_name,f1.FIELD_SYSTEM_ID,f1.system_name,f.if_type,IFNULL(f.job_cnt,0) "
			+ " FROM t_cook_bu_field_system F1 LEFT JOIN "
			+ " (SELECT t1.FIELD_SYSTEM_ID,'HTTP' AS if_type,COUNT(1) AS JOB_CNT FROM `t_esb_bu_if_http` t1 where t1.IS_ENABLE = '1' GROUP BY t1.FIELD_SYSTEM_ID"
			+ " UNION ALL "
			+ " SELECT t2.FIELD_SYSTEM_ID,'FTP' AS if_type,COUNT(1) AS JOB_CNT FROM `t_esb_bu_if_ftp` t2 where t2.IS_ENABLE = '1' GROUP BY T2.FIELD_SYSTEM_ID"
			+ " UNION ALL "
			+ " SELECT T3.FIELD_SYSTEM_ID,'KettleJob' AS IF_TYPE,COUNT(1) AS JOB_CNT FROM `t_cook_bu_field_job` t3 GROUP BY T3.FIELD_SYSTEM_ID"
			+ ") F ON  F.FIELD_SYSTEM_ID = F1.FIELD_SYSTEM_ID"
			+ " INNER JOIN t_cook_db_field F2 ON F2.FIELD_ID = F1.FIELD_ID"
			+ " INNER JOIN t_cook_bu_user_system F3 ON F3.FIELD_SYSTEM_ID = F1.FIELD_SYSTEM_ID"
			+ " WHERE ('' = :userId OR f3.USER_ID= :userId) AND"
			+ " ('' = :ifnull OR IFNULL(f.job_cnt,0) > :ifnull ) AND"
			+ " ('' = :nothing OR IFNULL(f.job_cnt,0) = :nothing ) AND"
			+ " ('' = :fieldId OR f2.field_id = :fieldId) AND"
			+ " ('' = :interfaceType OR f.if_type = :interfaceType) AND"
			+ " ('' = :fieldSystemId OR f1.FIELD_SYSTEM_ID = :fieldSystemId)"
			+ " LIMIT :page1,:page2")
	List<Object[]> countInterfaceBySystem(@Param("userId") String userId,
			@Param("fieldId") String fieldId,
			@Param("fieldSystemId") String fieldSystemId,
			@Param("ifnull") String ifnull,
			@Param("nothing") String nothing,
			@Param("interfaceType") String interfaceType,
			@Param("page1") int page1,
			@Param("page2") int page2);
	
	@Query(nativeQuery = true, value ="SELECT f2.field_id,f2.field_name,f1.FIELD_SYSTEM_ID,f1.system_name,f.if_type,IFNULL(f.job_cnt,0) "
			+ " FROM t_cook_bu_field_system F1 LEFT JOIN "
			+ " (SELECT t1.FIELD_SYSTEM_ID,'HTTP' AS if_type,COUNT(1) AS JOB_CNT FROM `t_esb_bu_if_http` t1 where t1.IS_ENABLE = '1' GROUP BY t1.FIELD_SYSTEM_ID"
			+ " UNION ALL "
			+ " SELECT t2.FIELD_SYSTEM_ID,'FTP' AS if_type,COUNT(1) AS JOB_CNT FROM `t_esb_bu_if_ftp` t2 where t2.IS_ENABLE = '1' GROUP BY T2.FIELD_SYSTEM_ID"
			+ " UNION ALL "
			+ " SELECT T3.FIELD_SYSTEM_ID,'KettleJob' AS IF_TYPE,COUNT(1) AS JOB_CNT FROM `t_cook_bu_field_job` t3 GROUP BY T3.FIELD_SYSTEM_ID"
			+ ") F ON  F.FIELD_SYSTEM_ID = F1.FIELD_SYSTEM_ID"
			+ " INNER JOIN t_cook_db_field F2 ON F2.FIELD_ID = F1.FIELD_ID"
			+ " INNER JOIN t_cook_bu_user_system F3 ON F3.FIELD_SYSTEM_ID = F1.FIELD_SYSTEM_ID"
			+ " WHERE ('' = :userId OR f3.USER_ID= :userId) AND"
			+ " ('' = :ifnull OR IFNULL(f.job_cnt,0) > :ifnull ) AND"
			+ " ('' = :nothing OR IFNULL(f.job_cnt,0) = :nothing ) AND"
			+ " ('' = :fieldId OR f2.field_id = :fieldId) AND"
			+ " ('' = :interfaceType OR f.if_type = :interfaceType) AND"
			+ " ('' = :fieldSystemId OR f1.FIELD_SYSTEM_ID = :fieldSystemId)")
	List<Object[]> countInterfaceBySystemForPage(@Param("userId") String userId,
			@Param("fieldId") String fieldId,
			@Param("fieldSystemId") String fieldSystemId,
			@Param("nothing") String nothing,
			@Param("interfaceType") String interfaceType,
			@Param("ifnull") String ifnull);
	
	//组织结构的展现
	@RestResource(exported = true)
	List<SystemInterface> findByOwnerFieldSystem_Id(String ownerFieldSystemId);
	
	@RestResource(exported = false)
	List<SystemInterface> findByOwnerFieldSystem_Field_Name(String fieldName);
	
	@RestResource(exported = false)
	Long countByOwnerFieldSystem_Id(String fieldSystemId);
	
	@RestResource(exported = false)
	SystemInterface findByKettleJob_Id(long kettleJobId);
	@RestResource(exported = false)
	Long countByJobName(String jobName);
	
	@RestResource(exported = false)
	Long countByJobCode(String jobCode);
	
	@RestResource(exported = false)
	List<SystemInterface> findByKettleJob_IdIn(Collection<Long> kettleJobIds);
	
//	long count();
//	
//	boolean exists(String id);
//	
//	@RestResource(exported = false)
//	<S extends SystemInterface> S save(S entity);	// could expose REST API
//	
//	<S extends SystemInterface> List<S> save(Iterable<S> entities);
//	
//	@RestResource(exported = false)
//	@RequiresPermissions(value={"fieldInterfaceHistoryInfoQuery:view"})
//	SystemInterface findOne(String id);	// could expose REST API
//	
//	SystemInterface getOne(String id);
//	
//	// 以下三个接口只能选一个暴露为REST API
//	
//	@RestResource(exported = false)
//	@RequiresPermissions(value={"fieldInterfaceHistoryInfoQuery:view"})
//	List<SystemInterface> findAll();	// could expose REST API
//	
//	@RestResource(exported = false)
//	@RequiresPermissions(value={"fieldInterfaceHistoryInfoQuery:view"})
//	List<SystemInterface> findAll(Sort sort);	// could expose REST API
//	
//	@RestResource(exported = true)
//	@RequiresPermissions(value={"fieldInterfaceHistoryInfoQuery:view"})
//	Page<SystemInterface> findAll(Pageable pageable);	// could expose REST API
//	
//	List<SystemInterface> findAll(Iterable<String> ids);
//	
//	// 以下两个接口，要么都暴露为REST API，要么都不暴露
//	@RestResource(exported = false)
//	@RequiresPermissions(value={"fieldInterfaceHistoryInfoQuery:view"})
//	void delete(String id);	// could expose REST API
//	
//	@RestResource(exported = false)
//	@RequiresPermissions(value={"fieldInterfaceHistoryInfoQuery:view"})
//	void delete(SystemInterface entity);	// could expose REST API
//	
//	void delete(Iterable<? extends SystemInterface> entities);
//	
//	void deleteAll();
//	
//	void flush();
//	
//	<S extends SystemInterface> S saveAndFlush(S entity);
//	
//	void deleteInBatch(Iterable<SystemInterface> entities);
//	
//	void deleteAllInBatch();
//	@RestResource(exported = false)
//	List<SystemInterface> findByUsers_UserCode(String userCode);
}
